package com.icesoft.core.web.helper.excel;

import com.icesoft.core.common.exception.CheckException;
import com.icesoft.core.common.util.DateUtils;
import lombok.NonNull;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStrings;
import org.apache.poi.xssf.model.Styles;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.xml.parsers.ParserConfigurationException;
import java.io.*;
import java.util.Collection;
import java.util.Map;

@Slf4j
public abstract class SaxExcelReader {
    /**
     * Should we output the formula, or the value it has?
     */
    private static boolean outputFormulaValues = true;

    /**
     * 初始化流监控
     *
     * @param excel
     * @return
     * @throws IOException
     */
    public static Collection<Map<String, String>> readExcelContent(File excel) throws IOException {
        long startTime = System.currentTimeMillis();
        String fileType = excel.getName().substring(excel.getName().lastIndexOf(".") + 1, excel.getName().length());
        Collection<Map<String, String>> dataMap;
        if (fileType.equals("xls")) {
            dataMap = processXls(excel);
        } else if (fileType.equals("xlsx")) {
            dataMap = processXlsx(excel);
        } else {
            throw new CheckException("读取的不是excel文件");
        }
        log.info("Sax import takes {} ms", System.currentTimeMillis() - startTime);
        return dataMap;
    }

    private static Collection<Map<String, String>> processXls(@NonNull File excel) throws IOException {
        try (POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(excel))) {
            // 这儿为所有类型的Record都注册了监听器，如果需求明确的话，可以用addListener方法，并指定所需的Record类型
            HSSFRequest request = new HSSFRequest();
            XlsHandler handler = new XlsHandler(outputFormulaValues);
            // 添加监听记录的事件
            MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(handler);

            FormatTrackingHSSFListener formatListener = new FormatTrackingHSSFListener(listener) {
                @Override
                public String formatNumberDateCell(CellValueRecordInterface cell) {
                    double value;
                    if (cell instanceof NumberRecord) {
                        value = ((NumberRecord) cell).getValue();
                        if (DateUtil.isADateFormat(getFormatIndex(cell), getFormatString(cell))
                                && DateUtil.isValidExcelDate(value)) {
                            return DateUtils.format(DateUtil.getJavaDate(value));// 日期处理
                        }
                    }
                    return super.formatNumberDateCell(cell);
                }

            }; // 监听代理，方便获取recordformat
            EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener = null;
            if (outputFormulaValues) {
                request.addListenerForAllRecords(formatListener);
            } else {
                workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
                request.addListenerForAllRecords(workbookBuildingListener);
            }
            handler.init(formatListener, workbookBuildingListener);
            // 创建时间工厂
            HSSFEventFactory factory = new HSSFEventFactory();
            factory.processWorkbookEvents(request, poifs);
            return handler.getDataMap();
        } catch (OfficeXmlFileException oxe) {
            throw new CheckException("文件后缀格式错误");
        }
    }

    /**
     * Initiates the processing of the XLS workbook file to CSV.
     *
     * @throws FileNotFoundException
     * @throws IOException           If reading the data from the package fails.
     * @throws SAXException          if parsing the XML data fails.
     */
    private static Collection<Map<String, String>> processXlsx(@NonNull File excel) throws IOException {
        try (OPCPackage xlsxPackage = OPCPackage.open(new FileInputStream(excel))) {
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage);
            XSSFReader xssfReader = new XSSFReader(xlsxPackage);
            StylesTable styles = xssfReader.getStylesTable();
            XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
            XlsxHandler xlsxHandler = new XlsxHandler();
            while (iter.hasNext()) {
                try (InputStream stream = iter.next()) {
                    processSheet(styles, strings, xlsxHandler, stream);
                }
            }
            return xlsxHandler.getDataMap();
        } catch (SAXException | OpenXML4JException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Parses and shows the content of one sheet using the specified styles and
     * shared-strings tables.
     *
     * @param styles           The table of styles that may be referenced by cells in the
     *                         sheet
     * @param strings          The table of strings that may be referenced by cells in the
     *                         sheet
     * @param sheetInputStream The stream to read the sheet-data from.
     * @throws IOException  An IO exception from the parser, possibly from a byte stream
     *                      or character stream supplied by the application.
     * @throws SAXException if parsing the XML data fails.
     */
    private static void processSheet(Styles styles, SharedStrings strings,
                                     XSSFSheetXMLHandler.SheetContentsHandler sheetHandler, InputStream sheetInputStream)
            throws IOException, SAXException {
        // 日期处理
        DataFormatter formatter = new DataFormatter() {
            @Override
            public String formatRawCellContents(double value, int formatIndex, String formatString) {
                if (DateUtil.isADateFormat(formatIndex, formatString) && DateUtil.isValidExcelDate(value)) {
                    return DateUtils.format(DateUtil.getJavaDate(value));
                }
                return super.formatRawCellContents(value, formatIndex, formatString);
            }

        };

        InputSource sheetSource = new InputSource(sheetInputStream);
        try {
            XMLReader sheetParser = SAXHelper.newXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } catch (ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
        }
    }

}
